﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.IO;
using System.Reflection;
using System.Collections.Generic;

namespace GeneratePE {
    internal class GenerateHelper {
        /// <summary>
        /// SqlServer is true, Access is false
        /// </summary>
        public static Boolean IsSqlServer { get; set; }
        /// <summary>
        /// Database connection string
        /// </summary>
        public static String ConnectionString { get; set; }

        /// <summary>
        /// 创建标准的 DataTable
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable CreateDataTable() {
            DataTable tableColumnDT = new DataTable();
            tableColumnDT.Columns.Add("Name", typeof(string));
            tableColumnDT.Columns.Add("Type", typeof(string));
            tableColumnDT.Columns.Add("Length", typeof(Int32));
            tableColumnDT.Columns.Add("Default", typeof(string));
            tableColumnDT.Columns.Add("Nullable", typeof(bool));
            tableColumnDT.Columns.Add("PrimaryKey", typeof(bool));
            tableColumnDT.Columns.Add("Remark", typeof(string));
            return tableColumnDT;
        }
        /// <summary>
        /// 获得指定表的结构信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>DataTable</returns> 
        public static DataTable LoadTableSchema(string tableName) {
            return IsSqlServer 
                ? LoadTableSchemaSqlServer(tableName)
                : LoadTableSchemaAccess(tableName);
        }
        /// <summary>
        /// 替换正则表达式指定的内容
        /// </summary>
        /// <param name="pattern">正则表达式</param>
        /// <param name="sourceString">源</param>
        /// <param name="targetString">被替换内容</param>
        /// <returns>替换后的字符串</returns> 
        public static string RegexReplace(string pattern, string sourceString, string targetString) {
            Regex regex = new Regex(pattern, RegexOptions.Multiline);
            return regex.Replace(sourceString, targetString);
        }       
        /// <summary>
        /// 产生 tab 空
        /// </summary>
        /// <param name="tabNum">产生数量</param>
        /// <returns>产生后和连接字符串</returns>
        public static string GenerateTab(int tabNum) {
            string tab = string.Empty;
            for (var ii = 0; ii < tabNum; ii++) {
                tab += FormatSymbol.Tab;
            }

            return tab;
        }
        /// <summary>
        /// 文件模板
        /// </summary>
        /// <param name="resourceName">资源名称</param>
        /// <returns>string</returns> 
        public static string GetTemplateContent(string resourceName) {
            using (StreamReader sr = new StreamReader(
                Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))) {
                return sr.ReadToEnd();
            }
        }
        /// <summary>
        /// 文件模板
        /// </summary>
        /// <param name="resourceName">资源名称</param>
        /// <returns>List</returns>
        public static List<string> GetTemplateContentList(string resourceName) {
            using (StreamReader sr = new StreamReader(
                Assembly.GetEntryAssembly().GetManifestResourceStream(resourceName))) {
                List<string> _list = new List<string>();
                while (!sr.EndOfStream) {
                    _list.Add(sr.ReadLine());
                }
                return _list;
            }
        }

        // 获得指定表的结构信息 Sql Server        
        private static DataTable LoadTableSchemaSqlServer(string tableName) {
            using (SqlConnection conn = new SqlConnection(ConnectionString)) {
                conn.Open();
                // Columns
                string[] restrictionColumns = new string[] { null, null, tableName };
                DataTable dtColumns = conn.GetSchema("Columns", restrictionColumns);
                // Primary key
                string[] restrictionPrimaryKey = new string[] { null, null, tableName };
                DataTable dtPrimaryKey = conn.GetSchema("PrimaryKeys", restrictionPrimaryKey);
                // Remarks
                string[] restrictionRemarks = new string[] { tableName, null };
                DataTable dtRemarks = conn.GetSchema("Remarks", restrictionRemarks);
                // operation
                DataTable dt = CreateDataTable();
                foreach (DataRow dr in dtColumns.Select("1=1", "ORDINAL_POSITION ASC")) {
                    DataRow drNew = dt.NewRow();
                    drNew["Name"] = dr["COLUMN_NAME"];
                    drNew["Type"] = Enum.GetName(typeof(SqlDbType),
                        GetSqlDbTypeValueBySqlDatatype(dr["DATA_TYPE"]));
                    drNew["Length"] = dr["MAXIMUM_LENGTH"];
                    drNew["Default"] = dr["COLUMN_DEFAULT"];
                    drNew["Nullable"] = dr["IS_NULLABLE"];
                    drNew["PrimaryKey"] = false;
                    foreach (DataRow drPK in dtPrimaryKey.Rows) {
                        if (dr["COLUMN_NAME"].Equals(drPK["COLUMN_NAME"])) {
                            drNew["PrimaryKey"] = true;
                            break;
                        }
                    }
                    drNew["Remark"] = string.Empty;
                    foreach (DataRow drRemarks in dtRemarks.Rows) {
                        if (dr["COLUMN_NAME"].Equals(drRemarks["COLUMN_NAME"])) {
                            drNew["Remark"] = drRemarks["REMARKS"];
                            break;
                        }
                    }
                    dt.Rows.Add(drNew);
                }
                return dt;
            }
        }
        // 获得指定表的结构信息 Access        
        private static DataTable LoadTableSchemaAccess(string tableName) {
            using (OleDbConnection conn = new OleDbConnection(ConnectionString)) {
                conn.Open();
                // Columns
                string[] restrictionColumns = new string[] { null, null, tableName };
                DataTable dtColumns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictionColumns);
                // Primary key
                string[] restrictionPrimaryKey = new string[] { null, null, tableName };
                DataTable dtPrimaryKey = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, restrictionPrimaryKey);
                // operation
                DataTable dt = CreateDataTable();
                foreach (DataRow dr in dtColumns.Select("1=1", "ORDINAL_POSITION ASC")) {
                    DataRow drNew = dt.NewRow();
                    drNew["Name"] = dr["COLUMN_NAME"];
                    drNew["Type"] = Enum.GetName(typeof(OleDbType), (int)dr["DATA_TYPE"]);
                    drNew["Length"] = dr["CHARACTER_MAXIMUM_LENGTH"];
                    drNew["Default"] = dr["COLUMN_DEFAULT"];
                    drNew["Nullable"] = dr["IS_NULLABLE"];
                    drNew["PrimaryKey"] = false;
                    foreach (DataRow drPK in dtPrimaryKey.Rows) {
                        if (dr["COLUMN_NAME"].Equals(drPK["COLUMN_NAME"])) {
                            drNew["PrimaryKey"] = true;
                            break;
                        }
                    }
                    drNew["Remark"] = dr["DESCRIPTION"];
                    dt.Rows.Add(drNew);
                }
                return dt;
            }
        }
        // 根据 sql data type 返回 SqlDbType 枚举值
        private static object GetSqlDbTypeValueBySqlDatatype(object sqlDataType) {
            switch (sqlDataType.ToString().ToLower()) {
                case "bigint": return 0;
                case "binary": return 1;
                case "bit": return 2;
                case "char": return 3;
                case "datetime": return 4;
                case "decimal": return 5;
                case "float": return 6;
                case "image": return 7;
                case "int": return 8;
                case "money": return 9;
                case "nchar": return 10;
                case "ntext": return 11;
                case "numeric": return 5;
                case "nvarchar": return 12;
                case "real": return 13;
                case "smalldatetime": return 15;
                case "smallint": return 16;
                case "smallmoney": return 17;
                case "sql_variant": return 23;
                case "sysname": return 12;
                case "text": return 18;
                case "timestamp": return 19;
                case "tinyint": return 20;
                case "uniqueidentifier": return 14;
                case "varbinary": return 21;
                case "varchar": return 22;
                case "xml": return 25;
                default: return 12;
            }
        }
    }
}
